package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import com.dudu.bean.ParamBean;
import com.dudu.model.Menu;

import net.sf.json.JSONObject;


import core.CoreDao;
import core.db.DBConnectionUtil;

public class MenuDao {
	public int insert(CoreDao dao, Menu bean) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_menu(name, url, parent_id, tree_level, is_leaf, sort, icon, create_time)");
			sql.append("VALUES(?, ?, ?, ?, ?, ?, ?, sysdate())");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						bean.getName(), bean.getUrl(), bean.getParent_id(), bean.getTree_level(), bean.getIs_leaf(),
						bean.getSort(), bean.getIcon()					
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public List<Menu> queryUserMenusById(CoreDao dao, int id) {

		Connection con = null;
		List<Menu> result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"SELECT DISTINCT(m.id), m.create_time, m.icon, m.is_leaf, m.`name`, m.parent_id, m.sort, m.tree_level, m.url " +
					"from t_menu m, t_role_menu rm, t_role_user ru " +
					"WHERE m.id = rm.menu_id and rm.role_id = ru.role_id " +
					"and ru.user_id = ? order by m.sort asc");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { id });
			if (list != null && list.size() > 0) {
				result = dao.covertToBean(list, Menu.class);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public Menu queryById(CoreDao dao, int id) {

		Connection con = null;
		Menu result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select id, name, url, parent_id, tree_level, is_leaf, sort, icon, create_time FROM t_menu where id=?");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { id });
			if (list != null && list.size() > 0) {
				List<Menu> items = dao.covertToBean(list, Menu.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public List<JSONObject> queryAll(CoreDao dao) {

		Connection con = null;
		List<JSONObject> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" select * from t_menu order by sort asc ");
			result = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { null });

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public List<ParamBean> queryMenuName(CoreDao dao, int level) {
		Connection con = null;
		List<ParamBean> result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"select id, name as text FROM t_menu where tree_level=? order by sort asc ");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { level });
			if (list != null && list.size() > 0) {
				List<ParamBean> items = dao.covertToBean(list, ParamBean.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public int update(CoreDao dao, Menu bean) {

		Connection con = null;
		int result = -1;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" UPDATE t_menu set name=?, url=?, parent_id=?, tree_level=?, is_leaf=?, sort=?, icon=? ");
			sql.append(" where id=?");

			List<JSONObject> list = dao.serviceJsonSql(

					con,
					sql.toString(),
					new Object[] { 
						bean.getName(), bean.getUrl(), bean.getParent_id(), bean.getTree_level(),
						bean.getIs_leaf(), bean.getSort(), bean.getIcon(), bean.getId()
					}

			);

			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
}
